At 17:48 +0200 on 21/10/98, Memphisto wrote:
> Noted, thanks. I'm a newbie in postgreSQL and happy that these types and
> functions exist, but I think the documentation is a bit spartan(lacks a
> lot of pieces of information) and depend on those bits that are there in
> the documentation.
The correction for the subqueries is not specific to PostgreSQL. My guess
is that you are not quite familiar with SQL in general. Perhaps you should
consider buying a book.
> Another question. Is there way to these truncation to weeks instead of
> months. As far as I know, postgreSQL does not support it.
Well, how does one truncate to weeks? Are Sundays in the next week or the
last week? This differs from culture to culture. For some, Friday is the
last day of the week...
Let's rephrase the question. You want to know if date D1 is within the same
week as date D2, given that a week starts on Sunday?
Well, calculate the day of week for Date D1, by taking date_part( 'dow', D1
). The result is an integer between 0 and 6.
Subtract that number of days from D1:
D1 - timespan( text( date_part( 'dow', D1 ) ) || ' days' );
You get the date of the Sunday on or before D1.
Do the same for D2. You get the date of the Sunday on or before D2.
Now see if you got the same date in both calculations...
Intricate, but you can define this in an SQL function, and avoid queries
which have too many parantheses for comfort.
CREATE FUNCTION sunday_of_date( datetime ) RETURNS datetime
AS 'SELECT $1 - timespan( text( date_part( ''dow'', $1 ) ) || '' days'' )'
LANGUAGE 'sql';
(Can't test this myself, because I only have Postgres 6.3.1, which doesn't
allow converting integer to text.)
Now, your queries will be something like:
SELECT *
FROM annex_log
WHERE sunday_of_date( login_start ) = sunday_of_date( 'now' );
If a week doesn't start on Sunday in your culture, you'll have to take the
result of the dow, add the appropriate number of days and take the modulo
of seven...
Herouth
--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma